Postgres query cancellation

Tackling query timeouts and cancellations in PostgresWhen working with Postgresyou will eventually need to cancel some queries to prevent queries from hogging too many resources when they run longer t...

Postgres query plan visualization tools

When you’ve got a slow Postgres query, EXPLAIN and its parameters are incredibly useful for working out why.However, the information returned can be difficult and time-consuming to interpret, especia...

PostgreSQL 18: Virtual generated columns

This is about another feature which was committed for PostgreSQL 18: Virtual generated columns. Generated columns are available already, but they need to be “STORED”. This means the result of an exp...

Just because you’re getting an index scan, doesn't mean you can’t do better!

An issue I often see folks missing when reviewing query plans, is that all of their scans involve indexes, so they think that the query is likely already as fast (or efficient) as it can be.Many know ...

Representing graphs in Postgresql

Let’s say we’ve got some graph-like data, such as a social network.We can represent this generically with two tables:createtablenodes( idserialprimarykey, nametext, detailsjsonb);createtableedges( i...

What Hurts in PostgreSQL Part One: Temporary Tables

PostgreSQL is one of the most powerful database systems in the world. I have always been passionate about its great power, especially its modern SQL language features.However, that doesn’t mean every...